package org.pomirkovany.model;

import java.awt.print.Book;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.pomirkovany.beans.Item;

public class ItemPeer {
	public static ArrayList getItemsByCategory(DataAccess dataAccess,
			String categoryId) {
		ArrayList<Item> items = new ArrayList<Item>();
		Connection connection = dataAccess.getConnection();
		if (connection != null) {
			try {
				Statement s = connection.createStatement();
				String sql = "select item_id, item_name, author, owner, adding_date, category_id, borrowed, "
						+ "picture, location, rate from items"
						+ " where category_id=" + categoryId;
				
				ResultSet rs = null;
				try {
					 rs = s.executeQuery(sql);
					
						while (rs.next()) {
							Item item = new Item();
							item.setId(rs.getString(1));
							item.setName(rs.getString(2));
							item.setAuthor(rs.getString(3));
							item.setOwner(rs.getString(4));
							item.setDate(rs.getDate(5));
							item.setCategory(rs.getInt(6));
							item.setBorrowed(rs.getBoolean(7));
							item.setPicture(rs.getString(8));
							item.setLocation(rs.getString(9));
							item.setRate(rs.getInt(10));

							items.add(item);
						}
					
						
					
				} finally {
					s.close();
					rs.close();
				}
			} catch (SQLException e) {
				System.out.println("Could not get items: " + e.getMessage());
			} finally {
				dataAccess.putConnection(connection);
			}
		}
		
		return items;
	}

	public static Item getItemById(DataAccess dataManager, String itemID) {
		Item item = null;
		Connection connection = dataManager.getConnection();
		if (connection != null) {
			try {
				Statement s = connection.createStatement();
				String sql = "select item_id, item_name, author, owner, adding_date, category_id, "
						+ "borrowed, picture, location, rate, item_comment from items"
						+ " where item_id=" + itemID;
				try {
					ResultSet rs = s.executeQuery(sql);
					if (rs.next()) {
						item = new Item();
						item.setId(rs.getString(1));
						item.setName(rs.getString(2));
						item.setAuthor(rs.getString(3));
						item.setOwner(rs.getString(4));
						item.setDate(rs.getDate(5));
						item.setCategory(rs.getInt(6));
						item.setBorrowed(rs.getBoolean(7));
						item.setPicture(rs.getString(8));
						item.setLocation(rs.getString(9));
						item.setRate(rs.getInt(10));
						item.setComment(rs.getString(11));

					}
				} finally {
					s.close();
				}
			} catch (SQLException e) {
				System.out.println("Could not get book: " + e.getMessage());
			} finally {
				dataManager.putConnection(connection);
			}
		}
		return item;
	}

	public static void geleteItemById(DataAccess dataManager, String itemId) {
		
		Connection connection = dataManager.getConnection();
		if (connection != null) {
			try {
				String sql = "delete from items where item_id = " + itemId;
				Statement s = connection.createStatement();
				try {
					s.executeUpdate(sql);
				} finally {
					s.close();
				}
			} catch (SQLException e) {
				System.out.println("Could not get item: " + e.getMessage());
			} finally {
				dataManager.putConnection(connection);
			}
		}
		System.out.println("item " + itemId + "deleted");
		
		
	}
	
	public static void addItem(DataAccess dataManager, Item item){
		
		Connection connection = dataManager.getConnection();
		if (connection != null) {
			try {
				String sql = "insert into items (item_name,author,owner,category_id,borrowed,picture," 
				+"location,rate,item_comment,adding_date) values('" + item.getName() + "','" + item.getAuthor() + "','"
				+item.getOwner()+"',"+item.getCategory()+","+item.isBorrowed()+",'"+item.getPicture() 
				+"','"+ item.getLocation() + "'," + item.getRate()+",'"+item.getComment()+"',CURDATE())";
			
				Statement s = connection.createStatement();
				try {
					s.executeUpdate(sql);
				} finally {
					s.close();
				}
			} catch (SQLException e) {
				System.out.println("Could not add item: " + e.getMessage());
			} finally {
				dataManager.putConnection(connection);
			}
		}
		System.out.println("item " + item.getName() + " added");
		
		
	}
	
	public static void editItem(DataAccess dataManager, Item item, String Id){
		
		Connection connection = dataManager.getConnection();
		if (connection != null) {
			try {
				String sql = "update items set item_name = '" + item.getName() + "', author = '" + item.getAuthor()
				+ "', owner = '" + item.getOwner() + "', category_id = " + item.getCategory() + ", borrowed = " + item.isBorrowed()
				+ ", picture = '" + item.getPicture() + "', location = '" + item.getLocation() + "', rate = " + item.getRate()
				+ ", item_comment = '" + item.getComment() + "', adding_date = CURDATE()"
				+ " where item_id = " + Id;
				
			
				Statement s = connection.createStatement();
				try {
					s.executeUpdate(sql);
				} finally {
					s.close();
				}
			} catch (SQLException e) {
				System.out.println("Could not edit item: " + e.getMessage());
			} finally {
				dataManager.putConnection(connection);
			}
		}
		System.out.println("item " + item.getName() + " edited");
		
		
	}
	
	public static ArrayList searchItems(DataAccess dataManager,
		      String keyword) {
				ArrayList<Item> items = new ArrayList<Item>();
				Connection connection = dataManager.getConnection();
				if (connection != null) {
				  try {
					  Statement s = connection.createStatement();
					  String sql = "select item_id, item_name, author, owner, adding_date, category_id, "
								+ "borrowed, picture, location, rate, item_comment from items"
							+ " where item_name like '%" + keyword.trim() + "%'"
							+ " or author like '%" + keyword.trim() + "%'"
							+ " or owner like '%" + keyword.trim() + "%'"
							+ " or location like '%" + keyword.trim() + "%'"
							+ " or item_comment like '%" + keyword.trim() + "%'";
		       
					ResultSet rs = null;  
					try {
		  			  rs = s.executeQuery(sql);
		         
		  	  		  while (rs.next()) {
		  	  			Item item = new Item();
						item.setId(rs.getString(1));
						item.setName(rs.getString(2));
						item.setAuthor(rs.getString(3));
						item.setOwner(rs.getString(4));
						item.setDate(rs.getDate(5));
						item.setCategory(rs.getInt(6));
						item.setBorrowed(rs.getBoolean(7));
						item.setPicture(rs.getString(8));
						item.setLocation(rs.getString(9));
						item.setRate(rs.getInt(10));
						item.setComment(rs.getString(11));
						
						items.add(item);
			  		      }
		            
				  	   ; 
		          }
		        finally { s.close(); 
		        		rs.close();}
				    }
		      catch (SQLException e) {
					  System.out.println("Could not search for items:" + e.getMessage());
				    }
		      finally {
		        dataManager.putConnection(connection);
		        }
		      }
				if(items.size() > 0)
				System.out.println("searching!" + items.get(0).getName());
				else
				System.out.println("nothing was found! " + keyword);
				return items;
				
			  }


		

		
	

}
